USE SAS
GO


/*

	select * from TR_PARTNER ORDER BY 1
	SELECT * FROM TR_ACTIVITYTASK ORDER BY 1
	SELECT * FROM TR_RELATIONSHIPS ORDER BY 1
	SELECT * FROM TR_ADDRESS ORDER BY 1
	SELECT * FROM TR_USER ORDER BY 1
	SELECT * FROM TR_CONTACT ORDER BY 1
	SELECT * FROM TR_COMMUNICATION ORDER BY 1
	SELECT * FROM TR_EMAIL ORDER BY 1
	SELECT * FROM TR_NOTE ORDER BY 1
*/

DELETE FROM TR_OPPORTUNITYI
DELETE FROM TR_RELATIONSHIPS
DELETE FROM TR_USER
DELETE FROM TR_CONTACT
DELETE FROM TR_COMMUNICATION
DELETE FROM TR_ADDRESS
DELETE FROM TR_EMAIL
DELETE FROM TR_NOTE
DELETE FROM TR_OPPORTUNITYH
DELETE FROM TR_ACTIVITYTASK
DELETE FROM TR_PARTNER
DELETE FROM TR_PRODUCT
GO

-- INSERT USER's DATA
declare @id varchar(100)

set @id = CAST(NEWID() as VARCHAR(100))
exec InsUpdUserDetails 
	@USERNO			= '0'			-- 1
,	@USERGUID		= @id
,	@FIRSTNAME		= 'Admin'
,	@LASTNAME		= 'Brother'
,	@USERID			= '1'
,	@LOGINID		= 'administrator@brother.com'
,	@PASSWORD		= 'YnJvdGhlcg==' --'brother'
,	@ISACTIVE				= 'X'
,	@CANCREATEPARENTACCT	= 'X'
,	@CANEDITPRODUCTMASTER	= 'X'
,	@ISSUPERUSER			= 'X'

set @id = CAST(NEWID() as VARCHAR(100))
exec InsUpdUserDetails 
	@USERNO			= '0'			-- 2
,	@USERGUID		= @id
,	@FIRSTNAME		= 'David'
,	@LASTNAME		= 'Ritter'
,	@USERID			= '1'
,	@LOGINID		= 'David.Ritter@brother.com'
,	@PASSWORD		= 'YnJvdGhlcg==' --'brother'
,	@ISACTIVE				= 'X'
,	@CANCREATEPARENTACCT	= 'X'
,	@CANEDITPRODUCTMASTER	= 'X'
,	@ISSUPERUSER			= 'X'

set @id = CAST(NEWID() as VARCHAR(100))
exec InsUpdUserDetails 
	@USERNO			= '0'			-- 3
,	@USERGUID		= @id
,	@FIRSTNAME		= 'Ed'
,	@LASTNAME		= 'Soule'
,	@USERID			= '1'
,	@LOGINID		= 'Ed.Soule@brother.com'
,	@PASSWORD		= 'YnJvdGhlcg==' --'brother'
,	@ISACTIVE				= 'X'
,	@CANCREATEPARENTACCT	= 'X'
,	@CANEDITPRODUCTMASTER	= 'X'
,	@ISSUPERUSER			= 'X'


--	Charles.deVega@brother.com
set @id = CAST(NEWID() as VARCHAR(100))
exec InsUpdUserDetails 
	@USERNO			= '0'			-- 4
,	@USERGUID		= @id
,	@FIRSTNAME		= 'Charles'
,	@LASTNAME		= 'deVega'
,	@USERID			= '1'
,	@LOGINID		= 'Charles.deVega@brother.com'
,	@PASSWORD		= 'YnJvdGhlcg==' --'brother'
,	@ISACTIVE				= 'X'
,	@CANCREATEPARENTACCT	= 'X'
,	@CANEDITPRODUCTMASTER	= 'X'
,	@ISSUPERUSER			= 'X'

--	John.McCormack@brother.com
set @id = CAST(NEWID() as VARCHAR(100))
exec InsUpdUserDetails 
	@USERNO			= '0'			-- 5
,	@USERGUID		= @id
,	@FIRSTNAME		= 'John'
,	@LASTNAME		= 'McCormack'
,	@USERID			= '1'
,	@LOGINID		= 'John.McCormack@brother.com'
,	@PASSWORD		= 'YnJvdGhlcg==' --'brother'
,	@ISACTIVE				= 'X'
,	@CANCREATEPARENTACCT	= 'X'
,	@CANEDITPRODUCTMASTER	= 'X'
,	@ISSUPERUSER			= 'X'

--	Aron.Vetser@brother.com
set @id = CAST(NEWID() as VARCHAR(100))
exec InsUpdUserDetails 
	@USERNO			= '0'			-- 6
,	@USERGUID		= @id
,	@FIRSTNAME		= 'Aron'
,	@LASTNAME		= 'Vetser'
,	@USERID			= '1'
,	@LOGINID		= 'Aron.Vetser@brother.com'
,	@PASSWORD		= 'YnJvdGhlcg==' --'brother'
,	@ISACTIVE				= 'X'
,	@CANCREATEPARENTACCT	= 'X'
,	@CANEDITPRODUCTMASTER	= 'X'
,	@ISSUPERUSER			= 'X'

--	Frank.Lodi@brother.com
set @id = CAST(NEWID() as VARCHAR(100))
exec InsUpdUserDetails 
	@USERNO			= '0'			-- 7
,	@USERGUID		= @id
,	@FIRSTNAME		= 'Frank'
,	@LASTNAME		= 'Lodi'
,	@USERID			= '1'
,	@LOGINID		= 'Frank.Lodi@brother.com'
,	@PASSWORD		= 'YnJvdGhlcg==' --'brother'
,	@ISACTIVE				= 'X'
,	@CANCREATEPARENTACCT	= 'X'
,	@CANEDITPRODUCTMASTER	= 'X'
,	@ISSUPERUSER			= 'X'
GO

-- CREATE PARENT ACCOUNTS

DECLARE 
	@guid		varchar(40)
,	@index		INT
,	@name		varchar(50)
,	@notes		varchar(50)

set @index = 1

WHILE (@index <= 3)
BEGIN

	set @guid = CAST(NEWID() as VARCHAR(100))
	set @name = 'Parent Account : ' + CAST(@index AS VARCHAR)
	set @notes = 'Notes For ' + @name 
	
	exec InsUpdParentAccountDetails 
		@PARENTACCOUNTNO	= '0'
	,	@PARENTACCOUNTGUID	= @guid
	,	@ADDRESSNO			= '0'
	,	@ADDRESSGUID		= ''
	,	@COMMUNICATIONID	= '0'
	,	@PARENTACCOUNTNAME	= @name
	,	@STREET1			= 'Street Name'
	,	@CITY				= 'City Name'
	,	@STATE				= 'CA'
	,	@ZIP				= '00000'
	,	@PHONE				= '123 123 1234'
	,	@FAX				= '123 456 7890'
	,	@WEBSITE			= 'http://www.brother-usa.com'
	,	@WEBSITEDESC		= 'corporate website'
	,	@ISACTIVE			= 'X'
	,	@NOTEID				= '0'
	,	@NOTES				= @notes
	,	@USERID				= '1'

	SET @index = @index + 1

END
GO

-- CREATE RESELLER ACCOUNTS

DECLARE 
	@guid		varchar(40)
,	@index		INT
,	@name		varchar(50)
,	@notes		varchar(50)

set @index = 1

WHILE (@index <= 3)
BEGIN

	set @guid = CAST(NEWID() as VARCHAR(100))
	set @name = 'Reseller Account : ' + CAST(@index AS VARCHAR)
	set @notes = 'Notes For ' + @name 
	
	exec InsUpdResellerAccountDetails 
		@RESELLERACCOUNTNO		= '0'
	,	@RESELLERACCOUNTGUID	= @guid
	,	@ADDRESSNO				= '0'
	,	@ADDRESSGUID			= ''
	,	@COMMUNICATIONID		= '0'
	,	@RESELLERACCOUNTNAME	= @name
	,	@STREET1				= 'Street Name'
	,	@CITY					= 'City Name'
	,	@STATE					= 'CA'
	,	@ZIP					= '00000'
	,	@PHONE					= '123 123 1234'
	,	@FAX					= '123 456 7890'
	,	@WEBSITE				= 'http://www.brother-usa.com'
	,	@WEBSITEDESC			= 'corporate website'
	,	@ISACTIVE				= 'X'
	,	@NOTEID					= '0'
	,	@NOTES					= @notes
	,	@USERID					= '1'

	SET @index = @index + 1

END
GO

-- CREATE CHILD ACCOUNTS FOR EACH PARENT

DECLARE 
	@guid		varchar(40)
,	@index		INT
,	@index2		INT
,	@name		varchar(50)
,	@notes		varchar(50)
,	@parentAccNo	int
,	@resellerAccNo	int

set @index2 = 1

WHILE (@index2 <= 3)
BEGIN
	set @index = 1

	WHILE (@index <= 3)
	BEGIN

		SET @parentAccNo = @index + 7
		set @resellerAccNo = @index + 10
	
		set @guid = CAST(NEWID() as VARCHAR(100))
		set @name = 'Account_' + CAST(@parentAccNo AS VARCHAR) + '.' + CAST(@index2 AS VARCHAR)
		set @notes = 'Notes For ' + @name 

		exec InsUpdAccountDetails 
			@ACCOUNTNO				= '0'
		,	@ACCOUNTGUID			= @guid
		,	@ACCOUNTNAME			= @name
		,	@NOOFLOCATIONS			= '1'
		,	@ACCOUNTTYPEID			= '1'
		,	@STREET1				= 'STREET NAME'
		,	@CITY					= 'CITY NAME'
		,	@STATE					= 'CA'
		,	@ZIP					= '00000'
		,	@PHONE					= '111 111 1111'
		,	@FAX					= ''
		,	@WEBSITE				= NULL
		,	@VERTICALID				= '3'
		,	@REFERRALSOURCEID		= '8'
		,	@NOTEID					= '0'
		,	@NOTES					= @notes
		,	@ISACTIVE				= 'X'
		,	@USERID					= '1'
		,	@ADDRESSNO				= '0'
		,	@ADDRESSGUID			= ''
		,	@COMMUNICATIONID		= '0'
		,	@PARENTACCOUNTNO		= @parentAccNo
		,	@OWNERID				= '1'
		,	@PRIMARYCONTACTID		= ''
		,	@RESELLERID				= @resellerAccNo 

		SET @index = @index + 1
	END
	
	SET @index2 = @index2 + 1
END
GO

-- CREATE CONTACTS FOR EACH ACCOUNT

DECLARE 
	@guid		varchar(40)
,	@index		INT
,	@index2		INT
,	@name		varchar(50)
,	@notes		varchar(50)
,	@accNo		int
,	@email		varchar(100)
,	@totalAcc	INT
,	@accStartIndex	INT

select 
	@totalAcc = COUNT(*) 
,	@accStartIndex  = MIN(PartnerNo)
FROM TR_PARTNER
WHERE PartnerCategory = 2
AND	PartnerType = 2 

set @index2 = 1

WHILE (@index2 <= 4)
BEGIN
	set @index = 1

	WHILE (@index <= @totalAcc)
	BEGIN

		SET @accNo = @index + (@accStartIndex -1)
		set @guid = CAST(NEWID() as VARCHAR(100))
		set @name = 'C_' + CAST(@accNo AS VARCHAR)+ '.' + CAST(@index2 AS VARCHAR) 
		set @notes = 'Notes For ' + @name 
		set @email = @name+'@brother.com'

		exec InsUpdContactDetails
			@CONTACTNO		= '0'			-- 10
		,	@CONTACTGUID	= @guid
		,	@LASTNAME		= 'Contact'
		,	@FIRSTNAME		= @name
		,	@STREET1		= 'STREET NAME'
		,	@CITY			= 'CITY NAME'
		,	@STATE			= 'CA'
		,	@ZIP			= '00000'
		,	@JOBTITLE		= ''
		,	@DEPARTMENT		= ''
		,	@PHONE			= ''
		,	@FAX			= ''
		,	@CELLPHONE		= ''
		,	@EMAIL			= @email
		,	@NOTEID			= '0'
		,	@NOTES			= ''
		,	@ISACTIVE		= 'X'
		,	@ADDRESSNO		= ''
		,	@ADDRESSGUID	= ''
		,	@COMMUNICATIONID= ''
		,	@EMAILID		= ''
		,	@USERID			= '1'
		,	@OWNERID		= '1'
		,	@ISMAINCONTACT	= 'X'
		,	@RELATEDPARTNERNO= @accNo

		SET @index = @index + 1
	END
	
	SET @index2 = @index2 + 1
END
GO
